package com.pointcircle.estate.server.framework.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.pointcircle.core.IRepository;
import com.pointcircle.estate.server.framework.entity.Building;
import com.pointcircle.estate.server.framework.entity.Floor;

@Repository
public interface FloorRepository extends IRepository<Floor, String> {

	@Query("select o from Floor o where o.building = :building order by ABS(floorName)")
	List<Floor> findByBuilding(@Param("building") Building building);
	
	Floor findByBuildingAndFloorName(
		@Param("building") Building building, @Param("floorName") String floorName);
	
	@Query(value= "select building_id,round(if(max(num)<0,0,max(num)),0) maxNum,round(if(min(num)>0,0,-min(num)),0) minNum from (\r\n" + 
			"	SELECT *, @tmp=replace(replace(replace(replace(replace(replace(replace(replace(replace(floor_name,'一','1'),'二','2'),'三','3'),'四','4'),'五','5'),'六','6'),'七','7'),'八','8'),'九','9') tmp, \r\n" + 
			"		(case when floor_name REGEXP '^[0-9]+$' then CONVERT(floor_name,SIGNED) \r\n" + 
			"		when floor_name REGEXP '[0-9]$' then -reverse(-(-reverse(floor_name))) \r\n" + 
			"		when @tmp REGEXP '[0-9]$' then -reverse(-(-reverse(@tmp)))\r\n" + 
			"		when floor_name in ('地库','地下室') then -1 else 1 end) num \r\n" + 
			"	FROM tb_floor a,(select @tmp='') t where building_id=? \r\n" + 
			") b", nativeQuery = true)
	List<Object[]> findFloorCountByBuildingId(@Param("buildingId") String buildingId);
}
